-- @owner: @li-xin12345
-- @date: 2023/1/18
-- @testpoint: 设置query_dop=2时，执行含有自定义函数的复杂sql语句

--step1.创建自定义函数;expect:创建成功
create function func_query_dop_0074(text) returns int language sql immutable
as $$ select $1::int;$$;
/
--step2.创建表bmsql_item并插入数据;expect:创建成功，插入数据成功
drop table if exists bmsql_item;
create table bmsql_item (
i_id int not null,
i_name varchar(24),
i_price numeric(5,2),
i_data varchar( 50),
i_im_id int
);
insert into bmsql_item values ('1','sqltest_varchar_1','0.01','sqltest_varchar_1','1') ;
insert into bmsql_item values ('2','sqltest_varchar_2','0.02','sqltest_varchar_2','2') ;
insert into bmsql_item values ('3','sqltest_varchar_3','0.03','sqltest_varchar_3','3') ;
insert into bmsql_item values ('4','sqltest_varchar_4','0.04','sqltest_varchar_4','4') ;
insert into bmsql_item(i_id) values ('5');

--step3.创建表bmsql_warehouse并插入数据;expect:创建成功，插入数据成功
drop table if exists bmsql_warehouse;
create table bmsql_warehouse(
w_id int not null,
w_ytd numeric(12,2),
w_tax numeric(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9)
);
insert into bmsql_warehouse values('1','0.01','0.0001','sqltest_va','sqltest_varchar_1','sqltest_varchar_1','sqltest_varchar_1','sq','sqltest_b');
insert into bmsql_warehouse values('2','0.02','0.0002','sqltest_va','sqltest_varchar_2','sqltest_varchar_2','sqltest_varchar_2','sq','sqltest_b');
insert into bmsql_warehouse values('3','0.03','0.0003','sqltest_va','sqltest_varchar_3','sqltest_varchar_3','sqltest_varchar_3','sq','sqltest_b');
insert into bmsql_warehouse values('4','0.04','0.0004','sqltest_va','sqltest_varchar_4','sqltest_varchar_4','sqltest_varchar_4','sq','sqltest_b');
insert into bmsql_warehouse(w_id) values('5');

--step4.创建表bmsql_new_order并插入数据;expect:创建成功，插入数据成功
drop table if exists bmsql_new_order;
create table bmsql_new_order (
no_w_id int not null,
no_d_id int not null,
no_o_id int not null
);
insert into bmsql_new_order values('1','1','1');
insert into bmsql_new_order values('2','2','2');
insert into bmsql_new_order values('3','3','3');
insert into bmsql_new_order values('4','4','4');
insert into bmsql_new_order values('5','5','5');

--step5.设置query_dop=2时执行含有自定义函数的复杂sql语句;expect:成功
set query_dop = 1;
select count(*)
from (select distinct greatest (no_d_id,no_d_id,no_d_id)
from bmsql_new_order
where no_o_id not in ( with tmp as (select w_id from bmsql_warehouse where bmsql_new_order.no_w_id >2 or w_id >=3) select * from tmp)) tb1,
( select count(*) from bmsql_item group by i_im_id,i_im_id having i_im_id like func_query_dop_0074('0')
) tb2;

--step5.环境清理;expect:成功
drop function func_query_dop_0074;
drop table bmsql_item;
drop table bmsql_warehouse;
drop table bmsql_new_order;